<?php require_once(BASEPATH.'application/controllers/validaciones.php');
//Cliente
class concilia extends validaciones {
	
	function concilia(){
		parent::Controller(); 
		$this->load->library("rapyd");
	}

	function index(){
		redirect("finanzas/concilia/filteredgrid");
	}
	function filteredgrid(){
        
        $this->rapyd->load("datagrid2","datafilter");
        $this->rapyd->uri->keep_persistence();
        
        $filter = new DataFilter("Buscar y Filtrar Conciliaciones de Bancos");
                		
        $filter->mes = new dropdownField("Mes", "mes");
		$filter->mes->style='width:180px;';
        $filter->mes->option("","Todos");
		$filter->mes->option("01","01");
        $filter->mes->option("02","02");
        $filter->mes->option("03","03");
        $filter->mes->option("04","04");
        $filter->mes->option("05","05");
        $filter->mes->option("06","06");
        $filter->mes->option("07","07");
        $filter->mes->option("08","08");
        $filter->mes->option("09","09");
        $filter->mes->option("10","10");
        $filter->mes->option("11","11");
        $filter->mes->option("12","12");
    
        $filter->ano = new dropdownField("A&ntilde;o", "ano");
		$filter->ano->style='width:180px;';
        $filter->ano->option("","Todos");
		$filter->ano->option("2014","2014");
        $filter->ano->option("2015","2015");
        $filter->ano->option("2016","2016");
        $filter->ano->option("2017","2017");	
		
        $filter->banco = new dropdownField("Banco", "b.banco");
		$filter->banco->style='width:300px;';
        $filter->banco->option("","Todos");
		$filter->banco->options("SELECT codigo, descrip FROM serier ORDER BY codigo" );
		$filter->banco->group = "Informaci&oacute;n de Recepci&oacute;n";
        
		$filter->buttons("reset","search");
		$filter->build("dataformfiltro");
        
        function boton($tipo=''){
			return $tipo;
		}
		
		$grid = new DataGrid2("Resultado");
        $select=array('codigo','status','descrip','IF(status=1,b.bancof,0) bancof','IF(status=1,b.bancoi,0) bancoi','IF(status=1,b.inicial,0) inicial','IF(status=1,b.final,0) final','ano','mes','id','moneda','IF(status=1,bancoi-final,0) as dife');
		$grid->db->select($select);
		$grid->db->from('bancos AS a');
        $grid->db->join('conciliacion AS b','a.codigo=b.banco');
		$grid->order_by("id","DESC");
        $grid->use_function('boton');
		$grid->per_page=10;  
        
        $atts = array(
			 'width'      => '800',
			 'height'     => '600',
			 'scrollbars' => 'yes',
			 'status'     => 'yes',
			 'resizable'  => 'yes',
			 'screenx'    => '0',
			 'screeny'    => '0'
		 );
		
        $uri = img(array('src'=>'images/<boton><#status#></boton>.gif','border'=>'0','alt'=>'Estado','height'=>'12','title'=>'Estado'));
        $uri .= anchor('finanzas/concilia/conciliarb/<#codigo#>/<#mes#>/<#ano#>/<#id#>','<#codigo#>(<#mes#>/<#ano#>)');
        
        $uri_2  = anchor_popup('formatos/ver/CONCILIACION/<#codigo#>/<#mes#>/<#ano#>',img(array('src'=>'images/pdf_logo.gif','border'=>'0','alt'=>'Ver Html','height'=>'15','title'=>'Ver Html')),$atts);
		$uri_2 .= anchor_popup("formatos/verhtml/CONCILIACION/<#codigo#>/<#mes#>/<#ano#>",img(array('src'=>'images/html_icon.gif','border'=>'0','alt'=>'Ver Pdf','height'=>'15','title'=>'Ver Pdf')),$atts);
					
		$grid->column('Acci&oacute;n',$uri_2,"align='center'");
		
        $grid->column("Numero"              ,$uri         ,"align='center'");
        $grid->column("Banco"               ,"codigo"         ,"align='center'");
        $grid->column("Nombre"              ,"descrip"    ,"align='left'");
        $grid->column("Moneda"              ,"moneda"    ,"align='center'");
        $grid->column("Mes"                 ,"mes"       ,"align='center'");
        $grid->column("A&ntilde;o"          ,"ano"       ,"align='center'");
        $grid->column("Saldo Inicial Libro" ,"<nformat><#inicial#></nformat>"        ,"align='right'");
        $grid->column("Saldo Final Libro"   ,"<nformat><#final#></nformat>"          ,"align='right'");
        $grid->column("Saldo Inicial Banco" ,"<nformat><#bancoi#></nformat>"        ,"align='right'");
        $grid->column("Saldo Final Banco"   ,"<nformat><#bancof#></nformat>"          ,"align='right'");
        $grid->column("Diferencia Libro y Banco","<nformat><#dife#></nformat>"          ,"align='right'");
        //$grid->column("Conciliado"          ,"<nformat><#conciliado#></nformat>"     ,"align='right'");
       
		$grid->add("finanzas/concilia/dataedit/show/create",'Nueva Conciliaci&oacute;n');
        $grid->build('datagridST');
		//echo $grid->db->last_query();

        $data['filtro']  = $filter->output;
		$data['content'] = $grid->output;
		$data['title']   = heading("Conciliaci&oacute;n de Bancos");
		$data['script']  = script('jquery.js');
		$data["script"] .= script('superTables.js');
		$data['head']    = $this->rapyd->get_head();

		$this->load->view('view_ventanas', $data);
    }
    function dataedit($estado,$idconcilia){
        
        $this->rapyd->load("dataedit");	
        
        $edit = new DataEdit("Informaci&oacute;n no Contabilizada", "nocontab");
		$edit->back_url = site_url("finanzas/concilia/filteredgrid");
        
        if(($edit->_status=='show')||($edit->_status=='modify')){
            
            $id  =$edit->_dataobject->get("id");
            IF(!empty($id)){
            $idconcilia=$this->datasis->dameval("SELECT conciliacion FROM nocontab WHERE id='$id'");;
            }else{$idconcilia=$estado;}
            
        $mSQL_1=$this->db->query("SELECT banco,mes, ano FROM conciliacion WHERE id='$idconcilia'");
        $row = $mSQL_1->row();
        $banco=$row->banco;
        $mes=$row->mes;
        $ano=$row->ano;
        
        $edit->back_url = site_url("finanzas/concilia/conciliarb/$banco/$mes/$ano/$idconcilia");
        
        }
        
        
        

        $edit->conciliacion = new hiddenField(" ", 'conciliacion');	
        $edit->conciliacion->insertValue = $idconcilia;	
        		
        $edit->fecha = new DateonlyField('Fecha', 'fecha','Y-m-d');
		$edit->fecha->insertValue = date('Y-m-d');
		$edit->fecha->rule = 'required';
		$edit->fecha->size = 12;

        $edit->tipo = new dropdownField("Tipo", "tipo");
		$edit->tipo->style='width:180px;';
        $edit->tipo->option("","Todos");
		$edit->tipo->option("CARGO NO REGISTRADO","CARGO NO REGISTRADO");
        $edit->tipo->option("ABONO NO IDENTIFICADO","ABONO NO IDENTIFICADO");
        $edit->tipo->option("OTROS","OTROS");
		
		$edit->monto = new inputField('Monto', 'monto');
		$edit->monto->css_class ='inputnum';
		$edit->monto->size      = 10;

		$edit->usuario = new autoUpdateField('usuario',$this->session->userdata('usuario'),$this->session->userdata('usuario'));
        
        $edit->observa = new inputField('Observaci&oacute;n', 'observa');
		$edit->observa->size =60;
		$edit->observa->maxlength=100;  
		$edit->observa->rule = "strtoupper";		
        
		$edit->buttons("back","save","modify","undo");
		$edit->build();
               
		$data['content'] =$edit->output;
		$data['title']   =  heading("Informaci&oacute;n no Contabilizada");
		$data["head"]    = script("jquery.pack.js").script("plugins/jquery.numeric.pack.js").script("plugins/jquery.floatnumber.js").$this->rapyd->get_head();
		$this->load->view('view_ventanas', $data);
    } 
    function chexistecomp(){
 
        $banco=$this->input->post('banco');
        $mes  =$this->input->post('mes');
        $ano  =$this->input->post('ano');
        
		$chek=$this->datasis->dameval("SELECT COUNT(*) FROM conciliacion WHERE banco='$banco' AND mes='$mes' AND ano='$ano'");
        
		if ($chek > 0){
			$this->validation->set_message('chexistecomp',"Conciliaci&oacute;n del banco $banco para la fecha $mes/$ano ya registrada");
			return FALSE;
		}else {
  		return TRUE;
		}
	}
	function guardar($banco,$mes,$ano){
        
        $id         = $this->input->post('id');
        $idconcilia = $this->input->post('idconcilia');
        $cant       = $this->input->post('cant');
        $bancof     = $this->input->post('bancof');
        $bancoi     = $this->input->post('bancoi');
        $inicial    = $this->input->post('inicial');
        $final      = $this->input->post('final');
        $observa    = $this->input->post('observa');
        $status     = $this->input->post('status');
                
        if($final==0)redirect("finanzas/concilia/conciliarb/$banco/$mes/$ano/$idconcilia");
        
		$i=$o=0;
        
        $this->db->query("DELETE FROM itconcilia WHERE idconcilia='$idconcilia'");
                 
		while($o<$cant){
				
			$array=array('0'=>$id[$i]);
       
            $this->db->query("INSERT INTO itconcilia(comprobante,debitos,creditos,fecha,origen,idconcilia,tipo) SELECT CONCAT(tcomp,ccomp,'-',NCOMP) comp,IF(dc='D',IF(b.moneda='01',ROUND(monto,2),ROUND(monto_me,2)),0)debitos,
        IF(dc='C',IF(b.moneda='02',ROUND(monto,2),ROUND(monto_me,2)),0)creditos,fecha, IF(tabla='siigo',tabla,'fycoADMIN')tabla,'$idconcilia' as id,conciliado       
        FROM comprobantes as a 
        JOIN bancos as b on a.cuenta=b.cuenta WHERE numerador='$id[$i]'");
			
            $i++;
			$o++;
		}
        
        if($status=='CONCILIADO'){$status=1;}ELSE{$status=2;}
        $this->db->query("UPDATE conciliacion SET status='$status',observa='$observa',bancoi='$bancoi', bancof='$bancof',inicial='$inicial', final='$final' WHERE id='$idconcilia'");
		
        redirect("finanzas/concilia/imprimir/$banco/$mes/$ano/$idconcilia");
	}
    function imprimir($banco,$mes,$ano,$idconcilia){
        
        //$data['filtro']  = ' ';
        
        $url = site_url('formatos/verhtml/CONCILIACION/'.$banco.'/'.$mes.'/'.$ano);

        $data['content']  = '<h1>'.anchor("finanzas/concilia/conciliarb/$banco/$mes/$ano/$idconcilia",'Regresar').'</h1>';
        $data['content'] .= anchor("formatos/ver/CONCILIACION/$banco/$mes/$ano",'Imprimir');
        $data['content'].= "<iframe name='fordi' src ='$url' width='100%' height='450'><p>Tu navegador no soporta iframes.</p></iframe>";
		        
		$data['title']   = heading("Banco Conciliado");
		$data['script']  = script('jquery.js');
		$data["script"] .= script('superTables.js');
		$data['head']    = $this->rapyd->get_head();

		$this->load->view('view_ventanas', $data);
    
    }
    function cerrar($banco,$mes,$ano,$idconcilia){ 
         
        $this->db->query("UPDATE conciliacion SET status='1'  WHERE id='$idconcilia'");
        redirect("finanzas/concilia/conciliarb/$banco/$mes/$ano/$idconcilia");
    }
    function abrir($banco,$mes,$ano,$idconcilia){
                
        $this->db->query("UPDATE conciliacion SET status='2'  WHERE id='$idconcilia'");
        redirect("finanzas/concilia/conciliarb/$banco/$mes/$ano/$idconcilia");
    }
    function conciliarb($banco,$mes,$ano,$idconcilia){
        
        $cuenta=$this->datasis->dameval("SELECT cuenta FROM bancos WHERE codigo='$banco'");
        
        $mSQL=$this->db->query("SELECT if((c.nombre=' ' or  c.nombre IS NULL),d.nombre,c.nombre) clipro,TRIM(a.descrip)glosa,CONCAT(ccomp_cruce,'-',num_cruce) AS documento,numerador id,usuario,estampac,conciliado,CONCAT(tcomp,ccomp,'-',NCOMP) comp,IF(tabla='siigo',tabla,'fycoADMIN')tabla,fecha,cheque,
        IF(dc='C',IF(b.moneda='01',CAST(SUM(monto) AS DECIMAL(10,2)),CAST(SUM(monto_me) AS DECIMAL(10,2))),'0') AS creditos, 
        IF(dc='D',IF(b.moneda='01',CAST(SUM(monto) AS DECIMAL(10,2)),CAST(SUM(monto_me) AS DECIMAL(10,2))),'0') AS debitos,
        b.moneda,dc,conciliado 
        FROM comprobantes as a 
        JOIN bancos as b on a.cuenta=b.cuenta
        LEFT JOIN scli as c on a.nit=LPAD(c.rifci,13,0)
        LEFT JOIN sprv as d on a.nit=LPAD(d.rif,13,0)
        WHERE tcomp IN ('R','G') AND  MONTH(fecha)='$mes' AND YEAR(fecha)='$ano' and a.cuenta='$cuenta'
        GROUP BY a.tcomp,a.ncomp,a.ccomp,a.cuenta
        ORDER BY fecha,ncomp ASC"); 
        
        $mSQL_2=$this->db->query("SELECT * FROM nocontab WHERE conciliacion='$idconcilia'");
                
        IF(($mes=='01')&&($ano=='2014')){
			
			$inicial=$this->datasis->dameval("SELECT inicial FROM bancos WHERE codigo='$banco'");
			
		}else{
			
			IF($mes=='01'){
			
				$mess='12';
				$ano=$ano-1;
			
			}else{
				
				$mess=$mes-1;
				
			}
            
            $inicial=$this->datasis->dameval("SELECT  final      
			FROM conciliacion as a 
			WHERE banco='$banco' AND mes='$mess' AND ano='$ano'");
		
			
		}
        
        $santerior=$this->datasis->dameval("SELECT inicial FROM bancos WHERE codigo='$banco'");
        $nombre=$this->datasis->dameval("SELECT CONCAT(codigo,'-',descrip) FROM bancos WHERE codigo='$banco'");     
        $cuenta=$this->datasis->dameval("SELECT cuenta FROM bancos WHERE codigo='$banco'");
        
        $mSQL_1=$this->db->query("SELECT * FROM conciliacion WHERE id='$idconcilia'");
        $row = $mSQL_1->row();
        
       
        $conten['result'] =$mSQL->result();
        $conten['result2'] =$mSQL_2->result();
        $conten['cant']   =$mSQL->num_rows();
        $conten['nombre'] =$nombre;
        $conten['cuenta'] =$cuenta;
        $conten['banco']  =$banco;
        $conten['mes']    =$mes;
        $conten['ano']    =$ano;
        $conten['inicial'] =$inicial;
        $conten['final']   =$row->final;
        $conten['observa']  =$row->observa;
        $conten['bancoi']   =$row->bancoi;
        $conten['bancof']   =$row->bancof;
        $conten['status']   =$row->status;
        $conten['santerior'] =$santerior;
        $conten['idconcilia'] =$idconcilia;
        
        $data['content'] = $this->load->view('view_concilia', $conten,true);
        
		$data['title']   = heading("Conciliaci&oacute;n $mes/$ano");
		$data['script']  = script('nformat.js');
		$data['head']    = $this->rapyd->get_head();
		$this->load->view('view_ventanas', $data);
        
    }
}
?>
